package com.iemacs.android.crt;


import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class SentencesDataSource {

	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns = {
        MySQLiteHelper.COLUMN_ID,
        MySQLiteHelper.COLUMN_CHINESE_TEXT,
        MySQLiteHelper.COLUMN_RUSSIAN_TEXT,
        MySQLiteHelper.COLUMN_KEYWORDS,
        MySQLiteHelper.COLUMN_VOICE_PATH
    };

	public SentencesDataSource(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public List<Sentence> getAllSentences() {
		List<Sentence> list = new ArrayList<Sentence>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_SENTENCES,
				allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Sentence s = cursorToSentence(cursor);
			list.add(s);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return list;
	}


   	public List<Sentence> getSentencesByKeyword(String kw) {
		List<Sentence> list = new ArrayList<Sentence>();
        String sql = "SELECT _id, chinese_text, russian_text,keywords,voice_path FROM sentences "
            + "WHERE keywords like '%"+kw+"%'";
        
		Cursor cursor = database.rawQuery(sql,null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Sentence s = cursorToSentence(cursor);
			list.add(s);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		
		Log.w("SQL",list.toString());
		
		return list;
		
		
	}

   	public Sentence createSentence(String ct, String rt) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.COLUMN_CHINESE_TEXT, ct);
		values.put(MySQLiteHelper.COLUMN_RUSSIAN_TEXT, rt);
		long insertId = database.insert(MySQLiteHelper.TABLE_SENTENCES, null,
				values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_SENTENCES,
				allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
				null, null, null);
		cursor.moveToFirst();
		Sentence newSentence = cursorToSentence(cursor);
		cursor.close();
		return newSentence;
	}

	public void deleteSentence(Sentence sentence) {
		long id = sentence.getId();
		System.out.println("Sentence deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_SENTENCES, MySQLiteHelper.COLUMN_ID
				+ " = " + id, null);
	}


	private Sentence cursorToSentence(Cursor c) {
		Sentence s = new Sentence();
		s.setId(c.getLong(0));
		s.setChineseText(c.getString(1));
		s.setRussianText(c.getString(2));
		s.setKeywords(c.getString(3));
		s.setVoicePath(c.getString(4));
		return s;
	}
}
